/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Server.ImplementInterface;

import DAL.DBManager;
import Server.DTO.WithDraw_DTO;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author 10520_000
 */
public class WithDrawRepository {

    public static int Insert(WithDraw_DTO info) {
        int iRet = -1;
        int origin_accountbalance = 0;
        try {
            Connection con = DBManager.getInstance().getConnection();
            origin_accountbalance = (int) SavingBookRepository.FindByBookID(info.getBookID()).getAccountBalance();
            String sql_withdrawal = "insert into WITHDRAWAL values('"
                    + info.getWithdrawID() + "','"
                    + info.getBookID() + "','"
                    + info.getCustomerName() + "','"
                    + info.getIDNumber() + "',"
                    + info.getAmountOfMoney() + ",'"
                    + info.getWithdrawingDate() + "')";
            String sql_savingbook = "";
            if (origin_accountbalance - info.getAmountOfMoney() == 0) {
                sql_savingbook = "update SavingBook set "
                        + "AccountBalance =" + (origin_accountbalance - info.getAmountOfMoney()) + ","
                        + "SBStatus='false' where BookID='" + info.getBookID() + "'";
            } else {
                sql_savingbook = "update SavingBook set "
                        + "AccountBalance =" + (origin_accountbalance - info.getAmountOfMoney()) + " "
                        + "where BookID='" + info.getBookID() + "'";
            }

            con.createStatement().executeUpdate(sql_withdrawal);
            con.createStatement().executeUpdate(sql_savingbook);
            iRet = 1;
        } catch (SQLException se) {
            System.out.println(se);
        }

        return iRet;
    }

    //
    public static int Update(WithDraw_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "UPDATE WITHDRAWAL SET WithdrawalID= ?, WithdrawalDate= ?, BookID= ?, CustomerName= ?, IDNumber= ?, AmountOfMoney= ? WHERE TypeID= ?";
            PreparedStatement pstmt = con.prepareStatement(SQL);

            pstmt.setString(1, info.getWithdrawID());
            pstmt.setDate(2, (Date) info.getWithdrawingDate());
            pstmt.setString(3, info.getBookID());
            pstmt.setString(4, info.getCustomerName());
            pstmt.setString(5, info.getIDNumber());
            pstmt.setInt(6, info.getAmountOfMoney());
            iRet = pstmt.executeUpdate();

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }

        return iRet;
    }

    public static int Delete(WithDraw_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "DELETE WITHDRAWAL WHERE WithdrawalID= ?";
            PreparedStatement pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, info.getWithdrawID());

            iRet = pstmt.executeUpdate();

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return iRet;
    }

    public static void DeleteAll() {
        Connection con = DBManager.getInstance().getConnection();
        try {
            con.setAutoCommit(false);
            String SQL = "DELETE FROM WITHDRAWAL";
            PreparedStatement pstmt = con.prepareStatement(SQL);

            pstmt.executeUpdate();
            con.commit();
        } catch (SQLException se) {
            try {
                con.rollback();
            } catch (SQLException ise) {
            }
        } finally {
            try {
                con.setAutoCommit(true);
            } catch (SQLException fse) {
            }
        }
    }

    public static ArrayList FindAll() {
        ArrayList arr = new ArrayList();

        try {
            String sql = "SELECT * FROM WITHDRAWAL ORDER BY WithdrawallD";
            Connection con = DBManager.getInstance().getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                WithDraw_DTO info = new WithDraw_DTO();
                info.setWithdrawID(rs.getString("WithdrawallD"));
                info.setAmountOfMoney(rs.getInt("AmountOfMoney"));
                info.setBookID(rs.getString("BookID"));
                info.setCustomerName(rs.getString("CustomerName"));
                info.setIDNumber(rs.getString("IDNumber"));
                info.setWithdrawingDate(rs.getDate("WithdrawalDate"));

                arr.add(info);
            }

            stmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return arr;
    }

    public static ArrayList FindByWithdrawallD(String WithdrawallD) {
        ArrayList arr = new ArrayList();

        try {
            String QRY = "SELECT * FROM WITHDRAWAL WHERE WithdrawallD LIKE(?) ORDER BY WithdrawallD";
            Connection con = DBManager.getInstance().getConnection();
            PreparedStatement pstmt = con.prepareStatement(QRY);
            pstmt.setString(1, "%" + WithdrawallD + "%");
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                WithDraw_DTO info = new WithDraw_DTO();
                info.setWithdrawID(rs.getString("WithdrawallD"));
                info.setAmountOfMoney(rs.getInt("AmountOfMoney"));
                info.setBookID(rs.getString("BookID"));
                info.setCustomerName(rs.getString("CustomerName"));
                info.setIDNumber(rs.getString("IDNumber"));
                info.setWithdrawingDate(rs.getDate("WithdrawalDate"));

                arr.add(info);
            }

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return arr;
    }
    
    public static void main(String[] str)
    {
        WithDraw_DTO info = new WithDraw_DTO();
        info.setWithdrawID("");
        info.setBookID("MS00002");
        info.setCustomerName("cntt");
        info.setIDNumber("123");
        info.setAmountOfMoney(301300);
        info.setWithdrawingDate(new Date(2014, 4, 4));
        Insert(info);
    }
}
